

<?php

//$con = mysql_connect("localhost","amcnnc5","3prismcreation");
$con = mysql_connect("localhost","root","00000000");
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}


mysql_query("set character set 'utf8'");
          mysql_query("set names 'utf8'");
            //change to your database name
		mysql_select_db("amcnnc5_dev_amcnn") or 
		     die("Could not select database: " . mysql_error());


$sql ="CREATE PROCEDURE  prcd_createGSTables()";
$sql .= "BEGIN ";

$sql .= "DROP TABLE IF EXISTS `city`;";
$sql .= "DROP TABLE IF EXISTS `city`;";
$sql .="CREATE TABLE `city` (
  `cid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name_en_us` varchar(50) DEFAULT NULL,
  `name_zh_hk` varchar(60) NOT NULL,
  `name_zh_cn` varchar(60) DEFAULT NULL,
  `delete_flag` int(11) DEFAULT NULL COMMENT '1/0',
  PRIMARY KEY (`cid`),
  UNIQUE KEY `name_zh_hk` (`name_zh_hk`),
  KEY `name_en_us` (`name_en_us`),
  KEY `name_zk_cn` (`name_zh_cn`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";


$sql .="DROP TABLE IF EXISTS `event_type`;";
$sql .="DROP TABLE IF EXISTS `event_type`;";
$sql .="CREATE TABLE `event_type` (
  `etid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name_en_us` varchar(50) DEFAULT NULL,
  `name_zh_hk` varchar(60) NOT NULL,
  `name_zh_cn` varchar(60) DEFAULT NULL,
  `delete_flag` int(11) DEFAULT NULL,
  PRIMARY KEY (`etid`),
  UNIQUE KEY `name_zh_hk` (`name_zh_hk`),
  KEY `name_en_us` (`name_en_us`),
  KEY `name_zk_cn` (`name_zh_cn`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";


$sql .="DROP TABLE IF EXISTS `venue`;";
$sql .="DROP TABLE IF EXISTS `venue`;";
$sql .="CREATE TABLE `venue` (
  `vid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name_en_us` varchar(50) DEFAULT NULL,
  `name_zh_hk` varchar(50) NOT NULL,
  `name_zh_cn` varchar(50) DEFAULT NULL,
  `telephones` varchar(80) DEFAULT NULL,
  `urls` varchar(80) DEFAULT NULL,
  `address_en_us` varchar(80) DEFAULT NULL,
  `address_zh_hk` varchar(80) DEFAULT NULL,
  `address_zh_cn` varchar(80) DEFAULT NULL,
  `contact_person` varchar(20) DEFAULT NULL,
  `delete_flag` int(11) DEFAULT NULL,
  `cid` int(11) unsigned NOT NULL COMMENT 'Refence to city id',
  PRIMARY KEY (`vid`),
  UNIQUE KEY `name_zh_hk` (`name_zh_hk`), 
  KEY `name_en_us` (`name_en_us`),
  KEY `name_zh_cn` (`name_zh_cn`),
  KEY `cid` (`cid`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";

$sql .="DROP TABLE IF EXISTS `cs_parameter`;";
$sql .="DROP TABLE IF EXISTS `cs_parameter`;";
$sql .="CREATE TABLE `cs_parameter` (
  `parameter_code` varchar(150) NOT NULL,
  `parameter_value` varchar(150) NOT NULL,
  `description` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`parameter_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

$sql .="DROP TABLE IF EXISTS `event`;";
$sql .="DROP TABLE IF EXISTS `event`;";
$sql .="CREATE TABLE `event` (
  `eid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `festival` varchar(150) DEFAULT NULL,
  `subject` varchar(200) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `last_update_time` datetime DEFAULT NULL,
  `is_allday_event` smallint(6) NOT NULL,
  `color` varchar(10) DEFAULT NULL,
  `recurring_rule` varchar(500) DEFAULT NULL,
  `detail_url` varchar(250) DEFAULT NULL,
  `picture_paths` varchar(250) DEFAULT NULL,
  `flash_paths` varchar(250) DEFAULT NULL,
  `delete_flag` int(11) DEFAULT NULL,
  `etid` int(11) unsigned NOT NULL COMMENT 'Refence to event_type id',
  `vid` int(11) unsigned NOT NULL COMMENT 'Refence to venue id',
  `uid` int(10) unsigned DEFAULT NULL COMMENT 'Refence to drupal uses id',
  PRIMARY KEY (`eid`),
  KEY `etid` (`etid`),
  KEY `vid` (`vid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";


$sql .="DROP TABLE IF EXISTS `tmp_city`;";
$sql .="DROP TABLE IF EXISTS `tmp_city`;";
$sql .="CREATE TABLE `tmp_city` (
  `A` varchar(50) DEFAULT NULL,
  `B` varchar(60) NOT NULL,
  `C` varchar(60) DEFAULT NULL,
  `existCid` int(11) DEFAULT NULL,
  `checkResult` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

$sql .="DROP TABLE IF EXISTS `tmp_event_type`;";
$sql .="DROP TABLE IF EXISTS `tmp_event_type`;";
$sql .="CREATE TABLE `tmp_event_type` (
  `A` varchar(50) DEFAULT NULL,
  `B` varchar(60) NOT NULL,
  `C` varchar(60) DEFAULT NULL,
  `existEtid` int(11) DEFAULT NULL,
  `checkResult` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

$sql .="DROP TABLE IF EXISTS `tmp_venue`;";
$sql .="DROP TABLE IF EXISTS `tmp_venue`;";
$sql .="CREATE TABLE `tmp_venue` (
  `A` varchar(150) DEFAULT NULL,
  `B` varchar(150) DEFAULT NULL,
  `C` varchar(150) DEFAULT NULL,
  `D` varchar(60) DEFAULT NULL,
  `E` varchar(240) DEFAULT NULL,
  `F` varchar(240) DEFAULT NULL,
  `G` varchar(240) DEFAULT NULL,
  `H` varchar(150) DEFAULT NULL,
  `I` varchar(240) DEFAULT NULL,
  `J` varchar(240) DEFAULT NULL,
  `existVid` int(11) DEFAULT NULL,
  `existCid` int(11) DEFAULT NULL,
  `checkResult` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

$sql .="DROP TABLE IF EXISTS `tmp_event`;";
$sql .="DROP TABLE IF EXISTS `tmp_event`;";
$sql .="CREATE TABLE `tmp_event` (
  `A` varchar(255) DEFAULT NULL,
  `B` varchar(255) DEFAULT NULL,
  `C` varchar(255) DEFAULT NULL,
  `D` varchar(255) DEFAULT NULL,
  `E` varchar(255) DEFAULT NULL,
  `F` varchar(255) DEFAULT NULL,
  `G` varchar(255) DEFAULT NULL,
  `H` varchar(255) DEFAULT NULL,
  `checkResult` int(11) DEFAULT NULL,
  `existEtid` int(11) DEFAULT NULL,
  `existVid` int(11) DEFAULT NULL,
  `existEid` int(11) DEFAULT NULL,
  `existCid` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

//$sql .="";
//$sql .="";

$sql .="insert  into `cs_parameter`(`parameter_code`,`parameter_value`,`description`) values 
    ('dbTablePrefix','amcnn_','數據表的前綴'),
    ('defaultTime','23:59:59','默認的Event結束時間'),
    ('downloadPath','/calendarservice/download/excel/','導出文件的臨時存放位置'),
    ('homePage','/?q=user','無權限登錄返回的頁面'),
    ('pageSize','10','分頁顯示時每頁顯示多少行'),
    ('uploadFileSize','300','可上傳的文件大小(KB)'),
    ('uploadPath','/calendarservice/upload/excel','導入文件的臨時存放位置'),
    ('xNumberDays','60','前後多少天'),
    ('imgUploadPath','/calendarservice/upload','圖片上傳路徑');";



$sql .= " END";

$sqlCall = "call prcd_createGSTables();";

$sqlDropProcd = "drop procedure if exists prcd_createGSTables";

//"drop procedure if exists prcd_".$dtName."_importExcel2007";

try {
//$db = new DBConnection();
    //$db->getConnection();
    
    if (mysql_query($sql,$con) == false)
    {
        echo '<br>Create procedure fail</br>';
        echo mysql_error();
    }
 else {
     echo '<br>Create procedure sucessfully</br>';
     
     if(mysql_query($sqlCall,$con) == false)
     {
         echo '<b>Call procedure fail</b>';
         echo mysql_error();
     }
 else {
         echo '<br>Call procedure sucessfully</br>';
     }
     
     if(mysql_query($sqlDropProcd,$con) == false)
     {
         echo '<br>Drop procedure fail</br>';
         echo mysql_error();
     }
 else {
         echo '<br>Drop procedure sucessfully</br>';
     }
    
}
}catch(Exception $e)
{
    echo $e->getMessage(); 
};

mysql_close($con);

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
?>
